package com.easyjava.builder;

import com.easyjava.bean.Constants;
import com.easyjava.bean.FieldInfo;
import com.easyjava.bean.TableInfo;
import com.easyjava.utils.PropertiesUtils;
import com.easyjava.utils.StringUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @program: workspace-easyjava
 * @description:
 * @author: cw
 * @create: 2024-05-08 10:28
 **/
public class BuildTable {

    private static final Logger log = LoggerFactory.getLogger(BuildTable.class);
    private static Connection conn = null;

    private static String SQL_SHOW_TABLE_STATUS = "show table status";
    private static String SQL_SHOW_TABLE_FIELDS = "show full FIELDS from %s";
    private static String SQL_SHOW_TABLE_INDEX = "show index from %s";

    static {
        String driverName = PropertiesUtils.getProperty("db.driver.name");
        String url = PropertiesUtils.getProperty("db.url");
        String user = PropertiesUtils.getProperty("db.username");
        String password = PropertiesUtils.getProperty("db.password");

        try {
            Class.forName(driverName);
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            log.error("数据库连接失败", e);
        }
    }

    public static List<TableInfo> getTables() {
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<TableInfo> tableInfoList = new ArrayList<>();
        try {
            ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
            rs = ps.executeQuery();
            while (rs.next()) {
                String tableName = rs.getString("name");
                String comment = rs.getString("comment");
                log.info("tableName:{}，comment:{}", tableName, comment);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                String beanName = tableName;
                if (Constants.IGNORE_TABLE_PERFIX) {
                    beanName = beanName.substring(beanName.indexOf("_") + 1);
                }
                beanName = processFiled(beanName, true);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanParamName(beanName + Constants.SUFFIX_BEAN_QUERY);

                // 读取字段信息
                readFieldInfo(tableInfo);
                // 读取索引信息
                readKeyIndexInfo(tableInfo);

                tableInfoList.add(tableInfo);
            }
        } catch (Exception e) {
            log.error("获取表失败", e);
        } finally {
            close(ps, rs);
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }

        return tableInfoList;
    }

    private static void close(PreparedStatement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception e) {
            }
        }
    }


    private static void readFieldInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        List<FieldInfo> fieldInfoList = new ArrayList<>();
        List<FieldInfo> fieldExtendList = new ArrayList<>();
        try {
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS, tableInfo.getTableName()));
            rs = ps.executeQuery();
            while (rs.next()) {
                String field = rs.getString("field");
                String type = rs.getString("type");
                String extra = rs.getString("extra");
                String comment = rs.getString("comment");
                String propertyName = processFiled(field, false);
                if (type.indexOf("(") > 0) {
                    type = type.substring(0, type.indexOf("("));
                }

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setFieldName(field);
                fieldInfo.setSqlType(type);
                fieldInfo.setComment(comment);
                fieldInfo.setAutoIncrement("auto_increment".equalsIgnoreCase(extra) ? true : false);
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(type));

                if (Constants.SQL_DATE_TIME_TYPES.contains(type)) {
                    tableInfo.setHaveDateTime(true);
                }
                if (Constants.SQL_DATE_TYPES.contains(type)) {
                    tableInfo.setHaveDate(true);
                }
                if (Constants.SQL_DECIMAL_TYPES.contains(type)) {
                    tableInfo.setHaveBigDecimal(true);
                }

                // String类型的参数
                if (fieldInfo.getJavaType().equals(Constants.TYPE_STRING)) {
                    buildProperty(fieldExtendList, propertyName + Constants.SUFFIX_BEAN_QUERY_FUZZY, field, type);
                }

                // 日期类型的参数
                if (fieldInfo.getJavaType().equals(Constants.TYPE_DATE)) {
                    buildProperty(fieldExtendList, propertyName + Constants.SUFFIX_BEAN_QUERY_TIME_START, field, type);
                    buildProperty(fieldExtendList, propertyName + Constants.SUFFIX_BEAN_QUERY_TIME_END, field, type);
                }

                fieldInfoList.add(fieldInfo);
            }

            // 循环的外面设置fieldInfoList
            tableInfo.setFieldList(fieldInfoList);
            // 循环的外面设置fieldExtendList
            tableInfo.setFieldExtendList(fieldExtendList);
        } catch (Exception e) {
            log.error("获取字段失败", e);
        } finally {
            close(ps, rs);
        }
    }

    private static void buildProperty(List<FieldInfo> fieldList, String propertyName, String field, String type) {
        FieldInfo fieldInfo = new FieldInfo();
        fieldInfo.setFieldName(field);
        fieldInfo.setJavaType(Constants.TYPE_STRING);
        fieldInfo.setPropertyName(propertyName);
        fieldInfo.setSqlType(type);
        fieldList.add(fieldInfo);
    }

    private static void readKeyIndexInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Map<String, FieldInfo> tempMap = new HashMap<>();
            for (FieldInfo fieldInfo : tableInfo.getFieldList()) {
                tempMap.put(fieldInfo.getFieldName(), fieldInfo);
            }

            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX, tableInfo.getTableName()));
            rs = ps.executeQuery();
            while (rs.next()) {
                String keyName = rs.getString("key_name");
                Integer nonUnique = rs.getInt("non_unique");
                String columnName = rs.getString("column_name");

                if (nonUnique == 1) {
                    continue;
                }
                List<FieldInfo> fieldInfos = tableInfo.getKeyIndexMap().get(keyName);
                if (null == fieldInfos) {
                    fieldInfos = new ArrayList<>();
                    tableInfo.getKeyIndexMap().put(keyName, fieldInfos);
                }
                if (tempMap.containsKey(columnName)) {
                    fieldInfos.add(tempMap.get(columnName));
                }
            }
        } catch (Exception e) {
            log.error("获取索引失败", e);
        } finally {
            close(ps, rs);
        }
    }

    private static String processFiled(String field, Boolean upperCaseFirstLetter) {
        StringBuffer sb = new StringBuffer();
        String[] fields = field.split("_");
        sb.append(upperCaseFirstLetter ? StringUtils.upperCaseFirst(fields[0]) : fields[0]);

        for (int i = 1, len = fields.length; i < len; i++) {
            sb.append(StringUtils.upperCaseFirst(fields[i]));
        }

        return sb.toString();
    }

    private static String processJavaType(String type) {
        if (Constants.sqlTypeMap.containsKey(type)) {
            return Constants.sqlTypeMap.get(type);
        } else {
            throw new RuntimeException("无法识别的类型：" + type);
        }
    }

}
